package com.lc.jooq.project.controller;

import com.lc.jooq.project.test.generated.Keys;
import com.lc.jooq.project.test.generated.tables.Author;
import com.lc.jooq.project.test.generated.tables.Book;
import com.lc.jooq.project.test.generated.tables.BookStore;
import com.lc.jooq.project.test.generated.tables.BookToBookStore;
import com.lc.jooq.project.test.generated.tables.records.AuthorRecord;
import com.lc.jooq.project.test.generated.tables.records.BookRecord;
import com.lc.jooq.project.test.generated.tables.records.BookStoreRecord;
import org.jooq.*;
import org.jooq.impl.DSL;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Map;

import static com.lc.jooq.project.test.generated.Tables.*;
import static java.lang.System.out;

/**
 * Created by liangchao03 on 2017/6/28.
 */
@RestController
public class BookController {
    @Autowired
    DSLContext context;
    @Autowired
    DataSourceTransactionManager txMgr;


    @RequestMapping("/user")
    public String getUser() throws SQLException {

        Result<Record> result = context.select().from(AUTHOR).fetch();
        Result<Record5<Integer, String, String, Integer, String>> result2 = context.select(AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, BOOK.ID, BOOK.TITLE).from(AUTHOR).rightJoin(BOOK).on(AUTHOR.ID.eq(BOOK.ID)).groupBy(AUTHOR.ID).orderBy(BOOK.ID.desc()).fetch();
        result2.stream().forEach(item -> {
            item.intoStream().forEach(value -> {
                System.out.println(value);
            });
        });


        Record record = result.get(0);
        System.out.println(result);
        System.out.println(record.getClass());
        for (Record r : result) {
            Integer id = r.getValue(AUTHOR.ID);
            String firstName = r.getValue(AUTHOR.FIRST_NAME);
            String lastName = r.getValue(AUTHOR.LAST_NAME);
            System.out.println("ID: " + id + " first name: " + firstName + " last name: " + lastName);
        }

        Book b = BOOK.as("b");
        Author a = AUTHOR.as("a");
        BookStore s = BOOK_STORE.as("s");
        BookToBookStore t = BOOK_TO_BOOK_STORE.as("t");


        context.select(a.FIRST_NAME, a.LAST_NAME)
                .from(a)
                .join(b).on(b.AUTHOR_ID.eq(a.ID))
                .join(t).on(t.BOOK_ID.eq(b.ID))
                .join(s).on(t.NAME.eq(s.NAME))
                .groupBy(a.FIRST_NAME, a.LAST_NAME)

                .fetch();


        System.out.println(txMgr);
        return "user";
    }

    @RequestMapping("/roll")
    public String rollback(@RequestParam("commit") boolean commit) {
        context.insertInto(BOOK_STORE).set(BOOK_STORE.NAME, ("ABC" + new SimpleDateFormat("hh-MM-ss").format(new Date()))).execute();
        TransactionStatus tx = txMgr.getTransaction(new DefaultTransactionDefinition());
        try {
            context.insertInto(BOOK_STORE).set(BOOK_STORE.NAME, "def" + new SimpleDateFormat("hh-MM-ss").format(new Date())).execute();
            if (commit) {
                txMgr.commit(tx);
            }
            throw new RuntimeException("Run Time");
        } catch (Exception e) {
            txMgr.rollback(tx);
            System.out.println("roll back");
        }
        return String.valueOf(context.fetchCount(BOOK));
    }

    @RequestMapping("/batch1")
    public String batch1() {
        Result<Record1<Integer>> result = context.select(DSL.max(AUTHOR.ID)).from(AUTHOR).fetch();
        int id = (int) result.getValue(0, 0);
        int[] count = context.batch(context.insertInto(AUTHOR, AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME).values(id + 1, "A" + (id + 1), "B" + (id + 1))
                , context.insertInto(AUTHOR, AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME).values(id + 2, "A" + (id + 2), "B" + (id + 2))
                , context.insertInto(AUTHOR, AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME).values(id + 3, "A" + (id + 3), "B" + (id + 3))).execute();
        return "batch1 sucess " + Arrays.toString(count);
    }

    @RequestMapping("/batch2")
    public String batch2() {
        Result<Record1<Integer>> result = context.select(DSL.max(AUTHOR.ID)).from(AUTHOR).fetch();
        int id = (int) result.getValue(0, 0);
        int[] count = context.batch(context.insertInto(AUTHOR, AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME).values((Integer) null, null, null))
                .bind(id + 1, "A" + (id + 1), "B" + (id + 1))
                .bind(id + 2, "A" + (id + 2), "B" + (id + 2))
                .bind(id + 3, "A" + (id + 3), "B" + (id + 3)).execute();
        return "batch2 sucess " + Arrays.toString(count);
    }

    @RequestMapping("/create")
    public String create() {

        return null;
    }

    @RequestMapping("/foreign")
    public String foreign(@RequestParam(name = "return",defaultValue = "false")boolean flag) {
        context.settings().withReturnAllOnUpdatableRecord(flag);

        Result<AuthorRecord> result = context.fetch(AUTHOR);
        result.stream().forEach(r -> {
            if (r.fetchChildren(Keys.FK_BOOK_AUTHOR).size() > 0) {
                r.setDistinguished(1);
                int val = r.store();
                out.println("Done:"+val);
            } else {
                if (r.get(AUTHOR.ID) > 5) {
                    r.set(AUTHOR.DATE_OF_BIRTH, new java.sql.Date(new Date().getTime()));
                    int val = context.update(AUTHOR).set(r).where(AUTHOR.ID.eq(r.get(AUTHOR.ID))).execute();
                    System.out.println("update:"+val);
                }
            }
        });

        return "Foreign  Done";
    }

    @RequestMapping("/one")
    public String one() {
        Record record = context.fetchOne("select * from author where id = 1");
        String sql = context.select(BOOK.TITLE, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
                .from(BOOK)
                .join(AUTHOR)
                .on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
                .where(BOOK.PUBLISHED_IN.eq(1948))
                .getSQL();
        AuthorRecord au1 = context.selectFrom(AUTHOR).where(AUTHOR.ID.eq(8)).fetchOne();
        au1.setId(9);
        au1.store();
        context.settings().withUpdatablePrimaryKeys(true);
        AuthorRecord au2 = context.selectFrom(AUTHOR).where(AUTHOR.ID.eq(7)).fetchOne();
        au2.setId(10);
        au2.store();

        return "one";
    }

    @RequestMapping("/list")
    public String list() {
        List<String> list1 = context.select().from(BOOK).fetch().getValues(BOOK.TITLE);
        List<Double> list2 = context.select().from(BOOK).fetch().getValues(BOOK.ID,Double.class);
        return "list:"+list1+" "+list2;
    }

    @RequestMapping("/map")
    public String map() {
        Map<Integer,String> map1 = context.select().from(BOOK).fetchMap(BOOK.ID,BOOK.TITLE);
        Map<Integer,String> map2 = context.selectFrom(BOOK).fetchMap(BOOK.ID,BOOK.TITLE);
        Map<Integer,BookRecord> map3 = context.selectFrom(BOOK).fetchMap(BOOK.ID);
        Map<Integer,Result<BookRecord>> map4 = context.selectFrom(BOOK).fetchGroups(BOOK.AUTHOR_ID);
        Map<Integer,List<Integer>> map5 = context.selectFrom(BOOK).fetchGroups(BOOK.ID,BOOK.AUTHOR_ID);
        Map<Integer,List<Integer>> map6 = context.selectFrom(BOOK).fetchGroups(BOOK.AUTHOR_ID,BOOK.LANGUAGE_ID);
        return "map:"+map1 +"\r\nmap2:"+map2+"\r\nmap3:"+map3+"\r\nmap4:"+map4+"\r\nmap5:"+map5+"\r\nmap6:"+map6;
    }

    @RequestMapping("/into")
    public String into(){
        Record record = context.select().from(AUTHOR).leftJoin(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID)).having(AUTHOR.ID.eq(1)).and(BOOK.ID.eq(1)).fetchOne();
        AuthorRecord authorRecord = record.into(AUTHOR);
        BookRecord bookRecord = record.into(BOOK);
        BookStoreRecord bookStoreRecord = record.into(BOOK_STORE);
        return authorRecord.toString() + bookRecord + bookStoreRecord;
    }
}
